"""
Case Type   : fullsql
Case Name   : 校验full sql关键字段不为负数（默认参数不会记录fullsql，设置为l1，l1会记录）
Create At   : 2022/01/29
Owner       : zhao-pengcheng-g
Description :
    1.设置level级别并查看,创建表,导入数据
    2.执行query
    3.查询系统视图,关键字段小于0的数据是空的
    4.环境清理
Expect      :
    1.成功
    2.成功
    3.成功
    4.环境清理成功
History     :
"""

import os
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common


class Fullsql(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        text = '---Opengauss_Function_Fullsql_Case0017 start---'
        self.logger.info(text)
        self.userNode = Node('PrimaryDbUser')
        self.rootNode = Node('PrimaryRoot')
        self.sh_primy = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.table_path = os.path.join(r'/home',
                                       self.userNode.ssh_user, 'copy_tables')
        self.table_name = 't_lineitem_fullsql_0017'
    

    def test_fullsql(self):
        text = '---prestep1:修改机器umask---'
        self.logger.info(text)
        umask_cmd = f"umask 0022;"
        self.rootNode.sh(umask_cmd).result()
        text = '---step1:设置level级别并查看，创建表,导入数据expect:成功---'
        self.logger.info(text)
        create_cmd = f'''select reset_unique_sql('global', 'all', 1);
            set track_stmt_stat_level='l1,l1';
            show track_stmt_stat_level;
            set current_schema to public;
            drop table if exists {self.table_name};
            create table {self.table_name}
            (
            l_orderkey    bigint not null, 
            l_partkey     bigint not null, 
            l_suppkey     bigint not null, 
            l_linenumber  bigint not null, 
            l_quantity    decimal(15,2) not null, 
            l_extendedprice  decimal(15,2) not null, 
            l_discount    decimal(15,2) not null, 
            l_tax         decimal(15,2) not null, 
            l_returnflag  char(1) not null, 
            l_linestatus  char(1) not null, 
            l_shipdate    date not null, 
            l_commitdate  date not null, 
            l_receiptdate date not null, 
            l_shipinstruct char(25) not null, 
            l_shipmode     char(10) not null, 
            l_comment      varchar(44) not null) 
            partition by range(l_shipdate)
            (
            partition l_shipdate_1 values less than('1993-01-01 00:00:00'),
            partition l_shipdate_2 values less than('1994-01-01 00:00:00'),
            partition l_shipdate_3 values less than('1995-01-01 00:00:00'),
            partition l_shipdate_4 values less than('1996-01-01 00:00:00'),
            partition l_shipdate_5 values less than('1997-01-01 00:00:00'),
            partition l_shipdate_6 values less than('1998-01-01 00:00:00'),
            partition l_shipdate_7 values less than('1999-01-01 00:00:00'));'''
        create_msg = self.sh_primy.execut_db_sql(create_cmd)
        self.logger.info(create_msg)
        self.assertIn('reset_unique_sql' and '(1 row)',
                      create_msg, '执行失败:' + text)
        self.assertTrue(create_msg.count(self.constant.SET_SUCCESS_MSG) == 2,
                        '执行失败:' + text)
        self.assertIn('l1,l1' and
                      self.constant.DROP_TABLE_SUCCESS and
                      self.constant.CREATE_TABLE_SUCCESS,
                      create_msg, '执行失败:' + text)
        mkdir_cmd = f'rm -rf {self.table_path};' \
                    f'mkdir {self.table_path}'
        self.userNode.sh(mkdir_cmd)
        self.common.scp_file(self.userNode, 'lineitem.tbl',
                             self.table_path)
        lineitem_path = os.path.join(self.table_path, 'lineitem.tbl')
        copy_cmd = f'''copy {self.table_name} from '{lineitem_path}' \
                    using delimiters '|'; 
                    analyze;'''
        self.logger.info(copy_cmd)
        copy_msg = self.sh_primy.execut_db_sql(copy_cmd)
        self.logger.info(copy_msg)
        self.assertIn(self.constant.copy_success_msg,
                      copy_msg, '执行失败:' + text)

        text = '---step2: 执行query,expect:成功:---'
        self.logger.info(text)
        sql_cmd = f'''select 
            l_returnflag,l_linestatus,sum(l_quantity) as sum_qty,
            sum(l_extendedprice) as sum_base_price,
            sum(l_extendedprice * (1 - l_discount)) as sum_l_disc_price,
            sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)/1000) 
            as sum_charge,avg(l_quantity) as avg_qty,
            avg(l_extendedprice) as avg_price,avg(l_discount) as avg_disc,
            count(*) as count_order 
            from {self.table_name} 
            where l_shipdate <= date '1996-01-01' - interval '3 day' 
            group by l_returnflag,l_linestatus 
            order by l_returnflag,l_linestatus;
            select pg_sleep(30);'''
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        self.assertIn('(4 rows)', sql_msg, '执行失败:' + text)

        text = '--step3: 查询系统视图,关键字段小于0的数据是空的,expect:成功:--'
        self.logger.info(text)
        select_cmd = f'''select count(*) > 0 from \
            dbe_perf.get_global_full_sql_by_timestamp\
            (now()-interval'10 min', now()) where unique_query_id= \
            (select unique_sql_id from dbe_perf.statement \
            where query like '%um_l_disc_price' and \
            (n_hard_parse<0 or n_returned_rows<0 or n_tuples_fetched<0 \
            or n_tuples_returned<0 or n_tuples_inserted<0 \
            or n_tuples_updated<0 or n_tuples_deleted<0 \
            or n_blocks_fetched<0 or n_blocks_hit <0 or n_soft_parse <0));'''
        select_msg = self.sh_primy.execut_db_sql(select_cmd,
                                                 dbname='postgres')
        self.logger.info(select_msg)
        self.common.equal_sql_mdg(select_msg,
                                  '?column?', 'f', '(1 row)', flag='1', )

    def tearDown(self):
        text = '---step4: 环境清理,expect:成功:---'
        self.logger.info(text)
        drop_cmd = f'drop table {self.table_name};'
        drop_msg = self.sh_primy.execut_db_sql(drop_cmd)
        self.logger.info(drop_msg)
        rm_cmd = f"rm -rf {self.table_path}"
        self.userNode.sh(rm_cmd).result()
        self.assertFalse(os.path.exists(self.table_path), '执行失败:' + text)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS,
                      drop_msg, '执行失败:' + text)
        self.logger.info(
            '---Opengauss_Function_Fullsql_Case0017 finish---')
